using System;
using System.Linq;
using Pay.DTO;
using System.Collections.Generic;
using Pay.Data;
using Pay.Service.Cache;
using Pay.Core;
using Pay.IService;
using Pay.TO;
using NPOI.HSSF.UserModel;
using System.IO;
using System.Threading.Tasks;

namespace Pay.Service {
	public class LogisticsService : ServiceBase, ILogisticsService, IDisposable {
		private IUnitOfWork _iUnitOfWork;
		private ILogisticsNOService _ilogisticsNOService;
		private IOrderService _iorderService;
		private ILogisticsDetailsService _logisticsDetailsService;
		private SxyPaySign _sxyPaySign;

		public LogisticsService(IUnitOfWork iUnitOfWork, ILogisticsNOService logisticsNOService, IOrderService orderService, ILogisticsDetailsService logisticsDetailsService) : base(iUnitOfWork) {
			_iUnitOfWork = iUnitOfWork;
			_ilogisticsNOService = logisticsNOService;
			_iorderService = orderService;
			_logisticsDetailsService = logisticsDetailsService;
			_sxyPaySign = new SxyPaySign();
		}

		/// <summary>
		/// 订单受理
		/// </summary>
		/// <param name="id"></param>
		/// <param name="userid"></param>
		/// <returns></returns>
		public string Post(long id, long userid) {
			string msg = "";
			if (id == 0)
				msg = "参数错误";
			else {
				var query = new LogiticsNOQuery();
				query.logiticsstatus = 1;
				var list = _ilogisticsNOService.Gets(query);
				if (list != null && list.Count > 0) {
					var model = new logistics_dto();
					model.payid = id;
					model.logisticsno = list[0].LogisticsNumber;
					model.logisticsstatus = LogisticsStatus.Taking;
					model.logisticstime = DateTime.Now;
					if (Add(model) > 0) {
						var logiticsnomodel = _ilogisticsNOService.Get(list[0].Id);
						logiticsnomodel.logisticsstatus = 2;
						if (_ilogisticsNOService.Update(logiticsnomodel) > 0) {
							var logisticsmodel = new logistics_details_dto();
							logisticsmodel.logisticsid = model.id;
							//logisticsmodel.logisticsstatus = LogisticsStatus.Taking;
							logisticsmodel.logisticstime = DateTime.Now;
							logisticsmodel.manageid = userid;
							var zz = _logisticsDetailsService.Add(logisticsmodel);

							_iorderService.Update(id,OrderStatus.Shipped,null);
							msg = "受理成功";
						} else {
							msg = "系统异常";
						}
					} else {
						msg = "系统异常";
					}
				} else {

					msg = "";
				}


			}

			return msg;

		}
		public IEnumerable<logistics_dto> Get() {
			return context.Logistics.FromCache<Logistics, logistics_dto>("Logistics");
		}
		public logistics_dto GetById(long id) {
			return context.Logistics.Where(x => x.Id == id).FromCacheFirstOrDefault<Data.Logistics, logistics_dto>();
		}
		/// <summary>
		/// 获取物流信息
		/// </summary>
		/// <param name="orderQuery"></param>
		/// <returns></returns>
		public PagedResult<logistics_dto> Gets(LogiticsQuery Query) {
			var filter = PredicateBuilder.True<Data.Logistics>();
		
			if (!string.IsNullOrEmpty(Query.flight_no))
				filter = filter.And(c => c.FlightNO == Query.flight_no);

			if (!string.IsNullOrEmpty(Query.postal_no))
				filter = filter.And(c => c.PostalNO == Query.postal_no);

			if (Query.merchant_id > 0) {
				var payids = context.Orders.Where(c => c.MerchantId == Query.merchant_id).Select(c => c.PayId).Distinct().ToList();
				//filter = filter.And(c => c.PayId);
				filter = filter.And(c => payids.Contains(c.PayId));
			}
			return context.Logistics.FromCachePaged<Data.Logistics, logistics_dto>(Query.page, Query.size, filter, orderBy => orderBy.OrderByDescending(c => c.LogisticsTime));
		}

		/// <summary>
		/// 新增物流信息
		/// </summary>
		/// <param name="dto"></param>
		public int Add(logistics_dto dto) {

			Logistics model = new Logistics();
			dto.ToDtoObject<Logistics>(model);
			context.Logistics.Add(model);
			int result = _iUnitOfWork.Commit("Logistics");
			dto.id = model.Id;
			return result;
		}

		/// <summary>
		/// 更新物流信息
		/// </summary>
		/// <param name="dto"></param>
		/// <returns></returns>
		public int Update(logistics_dto dto, long userid) {
			Logistics logistics = this.context.Logistics.FindById(dto.id);
			logistics.LogisticsStatus = dto.logisticsstatus;
			logistics.FlightNO = dto.flightno;
			logistics.LogisticsNO = dto.logisticsno;
			logistics.LogisticsTime = DateTime.Now.ToUnix();
			logistics.PayId = dto.payid;
			logistics.PostalNO = dto.postalno;
			logistics_details_dto logistics_details_dto = new logistics_details_dto();
			logistics_details_dto.logisticsid = logistics.Id;
			//logistics_details_dto.logisticsstatus = dto.logisticsstatus;
			logistics_details_dto.logisticstime = DateTime.Now;
			logistics_details_dto.manageid = userid;
			this._logisticsDetailsService.Add(logistics_details_dto);
			return this._iUnitOfWork.Commit("Logistics");
		}

		public List<logistics_details_dto> GetList(long id) {
			return context.LogisticsDetails.Where(x => x.LogisticsId == id).FromCache<LogisticsDetails, logistics_details_dto>();
		}
		/// <summary>
		/// 根据根据PayId获取物流信息获取物流信息
		/// </summary>
		/// <param name="PayId"></param>
		/// <returns></returns>
		public logistics_dto GetModelByPayId(long PayId) {
			return context.Logistics.Where(x => x.PayId == PayId).FromCacheFirstOrDefault<Logistics, logistics_dto>();
		}
        /// <summary>
        /// 向海关推送订单报文
        /// </summary>
        /// <returns><c>true</c>, if order was pushed, <c>false</c> otherwise.</returns>
        /// <param name="pay_id">Pay identifier.</param>
        public bool PushOrder(long pay_id) {
            string path = _iorderService.CreateOrderXml(pay_id);
            //return _iorderService.PushOrderXml(path);
            return true;
        }
		/// <summary>
		/// 向海关推送支付单报文
		/// </summary>
		/// <returns><c>true</c>, if pay order was pushed, <c>false</c> otherwise.</returns>
		/// <param name="pay_id">Pay identifier.</param>
		public async Task<bool> PushPayOrder(long pay_id) {
            decimal payAmount = 0;
            string orderNo = string.Empty;
            string transcationNo = string.Empty;

            var orders = context.Orders.Where(c => c.PayId == pay_id);
            payAmount = orders.Sum(c => c.PayAmount);
            transcationNo = orders.First().PayInfo.TransactionNo;
            orderNo = orders.First().PayInfo.OrderNo;

			var model = new sxy_customs_push {
				eStoreOrderNO = orderNo,
				orderNo = transcationNo,
				payAmount = (Convert.ToInt32(payAmount * 100)).ToString()
			};
			var result = await _sxyPaySign.PayGateway<sxy_customs_result_base>(model, "/jiaofei/CrossTrade_OrderSend.jsp");
            return (result.Status == "0" && result.retCode == "00") ? true : false;
		}
        /// <summary>
        /// 生成物流清单Excel
        /// </summary>
        /// <returns>The detail excel.</returns>
        /// <param name="pay_infos">Pay identifier.</param>
        public LogisticsExcel LogisticsDetailExcel(List<pay_info_dto> pay_infos) {
            string appTime = DateTime.Now.ToString("yyyy-MM-dd");
            var board_id = pay_infos[0].boardid;
            var logistics = context.logisticsLists.FirstOrDefault(c => c.Id == board_id);
            string excelName = string.Format("{0}清单{1}单",pay_infos[0].orders[0].shipping.ship_order_number, pay_infos.Count());
            HSSFWorkbook workbook2003 = new HSSFWorkbook();
            workbook2003.CreateSheet(excelName);
            HSSFSheet SheetOne = (HSSFSheet)workbook2003.GetSheet(excelName);
            for (int w = 0; w < 42; w++) {
                SheetOne.SetColumnWidth(w, 20 * 256);
            }

            #region 第一行
            HSSFRow SheetRow = (HSSFRow)SheetOne.CreateRow(0);
            HSSFCell[] SheetCell = new HSSFCell[12];
            for (int col = 0; col < 12; col++) {
                SheetCell[col] = (HSSFCell)SheetRow.CreateCell(col);
            }
            SheetCell[0].SetCellValue("提运单号:");
            SheetCell[1].SetCellValue(logistics.PostalNO);
            SheetCell[2].SetCellValue("起运国（地区）:");
            SheetCell[3].SetCellValue("台澎金马关税区");
            SheetCell[4].SetCellValue("进口日期:");
            SheetCell[5].SetCellValue(appTime);
            SheetCell[6].SetCellValue("运输方式:");
            SheetCell[7].SetCellValue("航空运输");
            SheetCell[8].SetCellValue("运输工具编号:");
            SheetCell[9].SetCellValue(logistics.FlightNO);
            SheetCell[10].SetCellValue("航班航次号:");
            SheetCell[11].SetCellValue(logistics.FlightNO);
            #endregion

            #region 第二行
            SheetRow = (HSSFRow)SheetOne.CreateRow(1);
            SheetCell = new HSSFCell[16];
            for (int col = 0; col < 16; col++) {
                SheetCell[col] = (HSSFCell)SheetRow.CreateCell(col);
            }
            SheetCell[0].SetCellValue("业务申报选择:");
            SheetCell[1].SetCellValue("BOTH");
            SheetCell[2].SetCellValue("贸易方式:");
            SheetCell[3].SetCellValue("9610");
            SheetCell[4].SetCellValue("申报日期:");
            SheetCell[5].SetCellValue(appTime);
            SheetCell[6].SetCellValue("申报海关代码:");
            SheetCell[7].SetCellValue("3713");
            SheetCell[8].SetCellValue("口岸海关代码:");
            SheetCell[9].SetCellValue("3713");
            SheetCell[10].SetCellValue("国检目的机构代码:");
            SheetCell[11].SetCellValue("390080");
            SheetCell[12].SetCellValue("国检施检机构代码:");
            SheetCell[13].SetCellValue("390080");
            #endregion

            #region 第三行
            SheetRow = (HSSFRow)SheetOne.CreateRow(2);
            SheetCell = new HSSFCell[10];
            for (int col = 0; col < 10; col++) {
                SheetCell[col] = (HSSFCell)SheetRow.CreateCell(col);
            }
            SheetCell[0].SetCellValue("电商平台名称:");
            SheetCell[1].SetCellValue("厦门创富互动网络科技有限公司");
            SheetCell[2].SetCellValue("物流企业名称:");
            SheetCell[3].SetCellValue("中外运空运发展股份有限公司厦门分公司");
            SheetCell[4].SetCellValue("担保企业名称:");
            SheetCell[5].SetCellValue("中外运空运发展股份有限公司厦门分公司");
            SheetCell[6].SetCellValue("申报企业名称:");
            SheetCell[7].SetCellValue("中外运空运发展股份有限公司厦门分公司");
            SheetCell[8].SetCellValue("监管场所编号:");
            SheetCell[9].SetCellValue("370120");
            #endregion

            #region 第四行
            SheetRow = (HSSFRow)SheetOne.CreateRow(3);
            SheetCell = new HSSFCell[42];
            for (int col = 0; col < 42; col++) {
                SheetCell[col] = (HSSFCell)SheetRow.CreateCell(col);
            }
            SheetCell[0].SetCellValue("序号");
            SheetCell[1].SetCellValue("企业内部编号");
            SheetCell[2].SetCellValue("物流运单号");
            SheetCell[3].SetCellValue("订单号");
            SheetCell[4].SetCellValue("电商企业名称");
            SheetCell[5].SetCellValue("主要货物信息");
            SheetCell[6].SetCellValue("毛重（公斤）");
            SheetCell[7].SetCellValue("净重（公斤）");
            SheetCell[8].SetCellValue("运费");
            SheetCell[9].SetCellValue("保费");

            SheetCell[10].SetCellValue("订购人姓名");
            SheetCell[11].SetCellValue("订购人电话");
            SheetCell[12].SetCellValue("订购人证件类型");
            SheetCell[13].SetCellValue("订购人证件号码");
            SheetCell[14].SetCellValue("收货人姓名");
            SheetCell[15].SetCellValue("收货人电话");
            SheetCell[16].SetCellValue("收货人证件类型");
            SheetCell[17].SetCellValue("收货人证件号码");
            SheetCell[18].SetCellValue("收件地址");
            SheetCell[19].SetCellValue("发货人");

            SheetCell[20].SetCellValue("发货人地址");
            SheetCell[21].SetCellValue("包装种类(海关)");
            SheetCell[22].SetCellValue("包装种类代码 (国检)");
            SheetCell[23].SetCellValue("是否包含木质包装");
            SheetCell[24].SetCellValue("备注");
            SheetCell[25].SetCellValue("商品序号");
            SheetCell[26].SetCellValue("企业商品货号");
            SheetCell[27].SetCellValue("企业商品品名");
            SheetCell[28].SetCellValue("商品编码");
            SheetCell[29].SetCellValue("商品规格型号");

            SheetCell[30].SetCellValue("条码");
            SheetCell[31].SetCellValue("原产国（地区）");
            SheetCell[32].SetCellValue("数量");
            SheetCell[33].SetCellValue("计量单位");
            SheetCell[34].SetCellValue("法定数量");
            SheetCell[35].SetCellValue("法定计量单位");
            SheetCell[36].SetCellValue("第二数量");
            SheetCell[37].SetCellValue("第二计量单位");
            SheetCell[38].SetCellValue("单价");
            SheetCell[39].SetCellValue("总价");

            SheetCell[40].SetCellValue("企业商品国检备案号");
            SheetCell[41].SetCellValue("商品备注");
            #endregion

            int row = 4, rowIndex = 1;
            int count = 0, num = 0;
            foreach (var pay_info in pay_infos) {
                var orders = context.Orders.Where(c => c.PayId == pay_info.id);
                string goodsInfo = string.Empty;
                double weight = 0;
                foreach (var order in orders) {
                    count++;
                    num = 0;
                    order.OrderItems.ForEach(item => {
                        weight += item.Product.Weight * item.Quantity;
                        goodsInfo += string.Format("{0}、", item.ProductName);
                    });
                    goodsInfo = goodsInfo.TrimEnd('、');
                    foreach (var item in order.OrderItems) {
                        num++;
                        SheetRow = (HSSFRow)SheetOne.CreateRow(row);
                        SheetCell = new HSSFCell[42];
                        for (int col = 0; col < 42; col++) {
                            SheetCell[col] = (HSSFCell)SheetRow.CreateCell(col);
                        }
                        SheetCell[0].SetCellValue(count.ToString());//序号
                        SetCellType(SheetCell[0]);
                        SheetCell[1].SetCellValue("");//企业内部编号
                        SheetCell[2].SetCellValue(pay_info.orders[0].shipping.ship_order_number);//物流运单号
                        SetCellType(SheetCell[2]);
                        SheetCell[3].SetCellValue(order.PayInfo.OrderNo);//订单号
                        SheetCell[4].SetCellValue("厦门创富互动网络科技有限公司");//电商企业名称
                        SheetCell[5].SetCellValue(goodsInfo);//主要货物信息
                        SheetCell[6].SetCellValue((item.GrossWeight ?? 0).ToString());//毛重（公斤）
                        SetCellType(SheetCell[6]);
                        SheetCell[7].SetCellValue(weight.ToString());//净重（公斤）
                        SetCellType(SheetCell[7]);
                        SheetCell[8].SetCellValue("0");//运费
                        SetCellType(SheetCell[8]);
                        SheetCell[9].SetCellValue("0");//保费
                        SetCellType(SheetCell[9]);
                        if (order.BuyId.HasValue) {
                            //var address = context.ShippingAddresses.FirstOrDefault(c => c.MerchantId == order.BuyId.Value);
                            var address = order.Shipping;
                            SheetCell[10].SetCellValue(address.RealName);//订购人姓名
                            SheetCell[11].SetCellValue(address.Phone);//订购人电话
                            SetCellType(SheetCell[11]);
                            SheetCell[12].SetCellValue("1");//订购人证件类型
                            SetCellType(SheetCell[12]);
                            SheetCell[13].SetCellValue(address.IdCard);//订购人证件号码
                            SetCellType(SheetCell[13]);
                        }
                        SheetCell[14].SetCellValue(order.Shipping.RealName);//收货人姓名
                        SheetCell[15].SetCellValue(order.Shipping.Phone);//收货人电话
                        SetCellType(SheetCell[15]);
                        SheetCell[16].SetCellValue("1");//收货人证件类型
                        SetCellType(SheetCell[16]);
                        SheetCell[17].SetCellValue(order.Shipping.IdCard);//收货人证件号码
                        SetCellType(SheetCell[17]);
                        SheetCell[18].SetCellValue(order.Shipping.RegionName.Replace(" ", "") + order.Shipping.Address);//收件地址
                        SheetCell[19].SetCellValue("李艮琪");//发货人
                        SheetCell[20].SetCellValue("台南市东区裕晨路370号");//发货人地址
                        SheetCell[21].SetCellValue("纸箱");//包装种类(海关)
                        SheetCell[22].SetCellValue("4M");//包装种类代码 (国检)
                        SheetCell[23].SetCellValue("N");//是否包含木质包装
                        SheetCell[24].SetCellValue("无");//备注
                        SheetCell[25].SetCellValue(num.ToString());//商品序号
                        SetCellType(SheetCell[25]);
                        SheetCell[26].SetCellValue(item.Product.GoodDeclaration.GoodSKU);//企业商品货号
                        SheetCell[27].SetCellValue(item.Product.GoodDeclaration.GoodName);//企业商品品名
                        SheetCell[28].SetCellValue(item.Product.GoodDeclaration.HSCode);//商品编码
                        SetCellType(SheetCell[28]);
                        SheetCell[29].SetCellValue(item.Product.GoodDeclaration.Model);//商品规格型号
                        SheetCell[30].SetCellValue(item.Product.GoodDeclaration.Barcode);//条码
                        SetCellType(SheetCell[30]);
                        SheetCell[31].SetCellValue(item.Product.GoodDeclaration.ProducCountry);//原产国（地区）
                        SheetCell[32].SetCellValue(item.Quantity.ToString());//数量
                        SetCellType(SheetCell[32]);
                        SheetCell[33].SetCellValue(item.Product.GoodDeclaration.MinUnit);//计量单位
                        SheetCell[34].SetCellValue(item.Product.Weight.ToString());//法定数量
                        SetCellType(SheetCell[34]);
                        SheetCell[35].SetCellValue(item.Product.WeightUnit);//法定计量单位
                        SheetCell[36].SetCellValue("");//第二数量
                        SheetCell[37].SetCellValue("");//第二计量单位
                        SheetCell[38].SetCellValue(item.UnitPrice.ToString());//单价
                        SetCellType(SheetCell[38]);
                        SheetCell[39].SetCellValue((item.Quantity * item.UnitPrice).ToString());//总价
                        SetCellType(SheetCell[39]);
                        SheetCell[40].SetCellValue(item.Product.GoodDeclaration.FileCode);//企业商品国检备案号
                        SheetCell[41].SetCellValue("");//商品备注
                        row++;
                        rowIndex++;
                    }
                }
            }
       

            using (MemoryStream ms = new MemoryStream()) {
                workbook2003.Write(ms);
                var result = new LogisticsExcel {
                    ms = ms,
                    ExcelName = string.Format("{0}清单{1}单.xlsx", DateTime.Now.ToString("yyyy-MM-dd"), pay_infos.Count())
                };
                return result;
            }
        }
        /// <summary>
        /// 设置单元格样式
        /// </summary>
        /// <param name="cell">Cell.</param>
        private void SetCellType(HSSFCell cell) {
			HSSFCellStyle cellStyle = (HSSFCellStyle)cell.CellStyle;
			cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
		}
        /// <summary>
        /// 生成物流运单Excel
        /// </summary>
        /// <returns>The no excel.</returns>
        /// <param name="pay_id">Pay identifier.</param>
		public LogisticsExcel LogisticsNoExcel(List<pay_info_dto> pay_infos) {
            var board_id = pay_infos[0].boardid;
            var logistics = context.logisticsLists.FirstOrDefault(c => c.Id == board_id); 
            string excelName = string.Format("{0}运单{1}单.xlsx", DateTime.Now.ToString("yyyy-MM-dd"), pay_infos.Count());
            HSSFWorkbook workbook2003 = new HSSFWorkbook();
            workbook2003.CreateSheet(excelName);
            HSSFSheet SheetOne = (HSSFSheet)workbook2003.GetSheet(excelName);
            for (int w = 0; w < 16; w++) {
                SheetOne.SetColumnWidth(w, 20 * 256);
            }
            #region 第一行
            HSSFRow SheetRow = (HSSFRow)SheetOne.CreateRow(0);   //创建10行  
            HSSFCell[] SheetCell = new HSSFCell[12];
            for (int col = 0; col < 12; col++) {
                SheetCell[col] = (HSSFCell)SheetRow.CreateCell(col);  //为第一行创建10个单元格  
            }
            SheetCell[0].SetCellValue("提运单号:");
            SheetCell[1].SetCellValue(logistics.PostalNO);
            SheetCell[2].SetCellValue("运输方式:");
            SheetCell[3].SetCellValue("航空运输");
            SheetCell[4].SetCellValue("航班航次号:");
            SheetCell[5].SetCellValue(logistics.FlightNO);
            SheetCell[6].SetCellValue("物流企业名称:");
            SheetCell[7].SetCellValue("中外运空运发展股份有限公司厦门分公司");
            SheetCell[8].SetCellValue("电商平台名称:");
            SheetCell[9].SetCellValue("厦门创富互动网络科技有限公司");
            SheetCell[10].SetCellValue("业务申报选择:");
            SheetCell[11].SetCellValue("BOTH");
            #endregion

            #region 第二行标题
            SheetRow = (HSSFRow)SheetOne.CreateRow(1);
            SheetCell = new HSSFCell[16];
            for (int col = 0; col < 16; col++) {
                SheetCell[col] = (HSSFCell)SheetRow.CreateCell(col);  //为第一行创建10个单元格  
            }
            SheetCell[0].SetCellValue("物流运单号");
            SheetCell[1].SetCellValue("订单号");
            SheetCell[2].SetCellValue("主要货物信息");
            SheetCell[3].SetCellValue("收货人姓名");
            SheetCell[4].SetCellValue("收货人电话");
            SheetCell[5].SetCellValue("收货地址");
            SheetCell[6].SetCellValue("毛重（公斤）");
            SheetCell[7].SetCellValue("净重（公斤）");
            SheetCell[8].SetCellValue("运费");
            SheetCell[9].SetCellValue("保价费");
            SheetCell[10].SetCellValue("发货人");
            SheetCell[11].SetCellValue("发货人电话");
            SheetCell[12].SetCellValue("发货人地址");
            SheetCell[13].SetCellValue("发货人所在国");
            SheetCell[14].SetCellValue("商品数量");
            SheetCell[15].SetCellValue("备注");
            #endregion

            int row = 2, quantity = 0;
            double grossWeight = 0, weight = 0;
          
            foreach (var pay_info in pay_infos) {
                var orders = context.Orders.Where(c => c.PayId == pay_info.id);
                string goodsInfo = string.Empty;
                weight = 0;
                foreach (var order in orders) {
                    SheetRow = (HSSFRow)SheetOne.CreateRow(row);
                    SheetCell = new HSSFCell[16];
                    for (int index = 0; index < 16; index++) {
                        SheetCell[index] = (HSSFCell)SheetRow.CreateCell(index);
                    }
                    quantity = order.OrderItems.Sum(c => c.Quantity);
                    grossWeight = order.OrderItems.Sum(c => c.GrossWeight ?? 0);
                    order.OrderItems.ForEach(item => {
                        weight += item.Product.Weight * item.Quantity;
                        goodsInfo += string.Format("{0}、", item.ProductName);
                    });
                    goodsInfo = goodsInfo.TrimEnd('、');
                    SheetCell[0].SetCellValue(pay_info.orders[0].shipping.ship_order_number);
                    SheetCell[1].SetCellValue(order.PayInfo.OrderNo);
                    SheetCell[2].SetCellValue(goodsInfo);
                    SheetCell[3].SetCellValue(order.Shipping.RealName);
                    SheetCell[4].SetCellValue(order.Shipping.Phone);
                    SetCellType(SheetCell[4]);
                    SheetCell[5].SetCellValue(order.Shipping.RegionName.Replace(" ", "") + order.Shipping.Address);
                    SheetCell[6].SetCellValue(grossWeight.ToString());
                    SetCellType(SheetCell[6]);
                    SheetCell[7].SetCellValue(weight.ToString());
                    SetCellType(SheetCell[7]);
                    SheetCell[8].SetCellValue(order.Freight.ToString());
                    SetCellType(SheetCell[8]);
                    SheetCell[9].SetCellValue("0");
                    SetCellType(SheetCell[9]);
                    SheetCell[10].SetCellValue("李艮琪");
                    SheetCell[11].SetCellValue("0925686999");
                    SetCellType(SheetCell[11]);
                    SheetCell[12].SetCellValue("台南市东区裕晨路370号");
                    SheetCell[13].SetCellValue("台澎金马关税区");
                    SheetCell[14].SetCellValue(quantity.ToString());
                    SetCellType(SheetCell[14]);
                    SheetCell[15].SetCellValue("");
                    row++;
                }
            }
         
            using (MemoryStream ms = new MemoryStream()) {
                workbook2003.Write(ms);
                var result = new LogisticsExcel {
                    ExcelName = excelName,
                    ms = ms
                };
                return result;
            }
        }
    }
}
